library(tidyverse)
library(readxl)
path = "files/1425challenge.xlsx"
input = read_excel(path, range = "B3:D6")
test = read_excel(path, range = "F3:G6")
result = input %>%
mutate(start = as.Date(str_replace(Start, "^M", ""), "%m-%Y"),
end = as.Date(str_replace(End, "^M", ""), "%m-%Y")) %>%
rowwise() %>%
mutate(Periods = paste(c(Start, format(seq(start + months(1), end - months(1), by = "1 month"), "M%m-%Y"), End), collapse = "; ")) %>%
ungroup() %>%
select(Project, Periods)
all.equal(result, test)
#> [1] TRUECrispo - Excel Challenge 15 2025
excel-challenges
weekly-exercises
Easy Sunday Excel Challenge
Challenge Description
Easy Sunday Excel Challenge
⭐ Problem Solution Project Start End Periods
Solutions
Logic:
Reads the workbook range needed for the challenge
Builds the intermediate helper columns that drive the final answer
Uses direct text-pattern extraction instead of manual cleanup
Strengths:
- The R solution stays compact and mirrors the workbook logic closely.
Areas for Improvement:
- The code assumes the workbook layout and named ranges remain stable.
Gem:
- The best part of the solution is choosing a tidy intermediate shape before producing the final answer.
import pandas as pd
import numpy as np
from datetime import datetime
path = "files/1425challenge.xlsx"
input = pd.read_excel(path, usecols="B:D", skiprows=2, nrows=4)
test = pd.read_excel(path, usecols="F:G", skiprows=2, nrows=4).rename(columns=lambda x: x.rstrip('.1'))
input[['start_month', 'start_year']] = input['Start'].str.extract(r'^M(\d{2})-(\d{4})')
input[['end_month', 'end_year']] = input['End'].str.extract(r'^M(\d{2})-(\d{4})')
input['start'] = pd.to_datetime(input['start_year'] + '-' + input['start_month'] + '-01')
input['end'] = pd.to_datetime(input['end_year'] + '-' + input['end_month'] + '-01')
input = input.drop(columns=['start_month', 'start_year', 'end_month', 'end_year'])
input['months'] = input.apply(lambda row: pd.date_range(row['start'], row['end'], freq='MS'), axis=1)
input = input.explode('months')
input['res'] = [
row['Start'] if month == row['start'] else (
row['End'] if month == row['end'] else month.strftime('M%m-%Y')
)
for _, row in input.iterrows() for month in [row['months']]
]
result = input.groupby('Project').agg(
Periods=('res', lambda x: '; '.join(x))
).reset_index()
print(test.equals(result)) # TrueLogic:
Reads the workbook range needed for the challenge
Aggregates or ranks values at the correct grouping level
Applies the rule iteratively until the output is complete
Strengths:
- The Python version keeps the same rule in a direct pandas-oriented workflow.
Areas for Improvement:
- As with the R version, any workbook layout change would require small adjustments.
Gem:
- The implementation stays close to the stated challenge instead of adding unnecessary complexity.
Difficulty Level
This task is moderate:
It combines familiar Excel-style logic with at least one non-trivial reshape, grouping, or parsing step.
The answer depends on getting the output layout exactly right.